package sk.naive.talker.server;

import sk.naive.talker.*;
import sk.naive.talker.persistence.*;
import sk.naive.talker.props.*;

import java.sql.*;
import java.util.logging.*;
import java.util.*;

/**
 * DB layer object for persistent (transactional) operations.
 * <p/>
 * Static part of this class provides simple connection pooling and
 * one simple instance for nontransactional operations. Finishing
 * methods like rollback or commit does NOTHING on this simple instance.
 * <p/>
 * Main usage of this class is for transactional purposes. Instance
 * new DbLayer object, call DB methods (non-static) on it and finally
 * commit it or rollback as appropriate. Object holds one private
 * connection which is then returned to pool. After commit or rollback
 * the object is henceforth unusable.
 *
 * @author <a href="mailto:virgo@naive.deepblue.sk">Richard "Virgo" Richter</a>
 * @version $Revision: 1.59 $ $Date: 2005/02/16 19:27:21 $
 */
public class DbLayer
	implements UserPersistence,
	ObjectPersistence,
	TextPersistence,
	LocationPersistence,
	BoardPersistence
{
	private static Logger logger;

	public static final int OBJECT_TYPE_TALKER = 0;
	public static final int OBJECT_TYPE_USER = 1;
	public static final int OBJECT_TYPE_TEXT = 2;
	public static final int OBJECT_TYPE_BOARD = 3;
	public static final int OBJECT_TYPE_LOCATION = 4;
	public static final int OBJECT_TYPE_THING = 5;

	public DbLayer() {
	}

// objects
	public void loadProperties(PersistentObject object) throws PersistenceException {
		PreparedStatement pstmt = null;
		try {
			pstmt = DbTransaction.getConnection().prepareStatement("select name, value from properties where id=?");
			pstmt.setInt(1, object.getId());
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				String key = rs.getString("name");
				String value = rs.getString("value");
				try {
					object.setStoredValue(key, value);
				} catch (PropertyStoreException e) {
					logger.warning("Invalid property value in DB (prop=" + key + ", value=" + value + ")!");
				}
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			forcedStatementClose(pstmt);
		}
	}

	public String loadProperty(Integer id, String name) throws PersistenceException {
		String val = null;
		PreparedStatement pstmt = null;
		try {
			pstmt = DbTransaction.getConnection().prepareStatement("select value from properties where id=? and name=?");
			pstmt.setInt(1, id);
			pstmt.setString(2, name);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				val = rs.getString("value");
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			forcedStatementClose(pstmt);
		}
		return val;
	}

	/**
	 * Performs update/insert of property or delete if value is null.
	 *
	 * @param id
	 * @param name
	 * @param value
	 * @throws PersistenceException
	 */
	public void storeProperty(Integer id, String name, String value) throws PersistenceException {
		PreparedStatement pstmt = null;
		try {
			if (value != null) {
				pstmt = DbTransaction.getConnection().prepareStatement("update properties set value=? where id=? and name=?");
				int i = 1;
				pstmt.setString(i++, value);
				pstmt.setInt(i++, id);
				pstmt.setString(i++, name);
				if (pstmt.executeUpdate() == 0) {
					pstmt.close();
					pstmt = DbTransaction.getConnection().prepareStatement("insert into properties (id, name, value) values (?, ?, ?)");
					i = 1;
					pstmt.setInt(i++, id);
					pstmt.setString(i++, name);
					pstmt.setString(i++, value);
					pstmt.executeUpdate();
				}
			} else {
				pstmt = DbTransaction.getConnection().prepareStatement("delete from properties where id=? and name=?");
				int i = 1;
				pstmt.setInt(i++, id);
				pstmt.setString(i++, name);
				pstmt.executeUpdate();
			}
			pstmt.close();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			forcedStatementClose(pstmt);
		}
	}

	private synchronized Integer getNewObjectId() throws PersistenceException {
		Integer retVal = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmtUpdate = null;
		try {
			pstmt = DbTransaction.getConnection().prepareStatement("select name, value from sequences where name='object_id' for update");
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			pstmtUpdate = DbTransaction.getConnection().prepareStatement("update sequences set value=value+1 where name='object_id'");
			pstmtUpdate.executeUpdate();
			pstmtUpdate.close();
			retVal = rs.getInt("value");
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			forcedStatementClose(pstmt);
			forcedStatementClose(pstmtUpdate);
		}
		return retVal;
	}

	public Integer create(int type) throws PersistenceException {
		Integer retVal = null;
		PreparedStatement pstmt = null;
		try {
			retVal = getNewObjectId();
			pstmt = DbTransaction.getConnection().prepareStatement("insert into objects values (?, ?)");
			pstmt.setInt(1, retVal);
			pstmt.setInt(2, type);
			pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			forcedStatementClose(pstmt);
		}
		return retVal;
	}

	public void remove(Integer id) throws PersistenceException {
		PreparedStatement pstmt = null;
		try {
			pstmt = DbTransaction.getConnection().prepareStatement("delete from objects where id=?");
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			forcedStatementClose(pstmt);
		}
	}

	public Integer objectId(int type, String namingProperty, String name) throws PersistenceException {
		PreparedStatement pstmt = null;
		Integer id = null;
		try {
			pstmt = DbTransaction.getConnection().prepareStatement("select" +
				"   objects.id" +
				" from" +
				"   objects,properties" +
				" where" +
				"   objects.id=properties.id" +
				"   and name=?" +
				"   and otype=?" +
				"   and value=?");
			int i = 1;
			pstmt.setString(i++, namingProperty);
			pstmt.setInt(i++, type);
			pstmt.setString(i++, name);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				id = rs.getInt("id");
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			forcedStatementClose(pstmt);
		}
		return id;
	}

	public Set<Integer> objectIdList(int type) throws PersistenceException {
		Set<Integer> set = new HashSet<Integer>();
		PreparedStatement pstmt = null;
		try {
			pstmt = DbTransaction.getConnection().prepareStatement("select" +
				"   objects.id" +
				" from" +
				"   objects" +
				" where" +
				"   otype=?");
			pstmt.setInt(1, type);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				set.add(rs.getInt("id"));
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			forcedStatementClose(pstmt);
		}
		return set;
	}

	private static String createFindAllObjectsOfTypeSelect(int objectType, String propertyName) {
		return "select" +
			"   p.id as id, p.value as value" +
			" from" +
			"   properties p INNER JOIN objects o ON (p.id = o.id and o.otype = " + objectType + ")" +
			" where" +
			"   p.name='" + propertyName + "' order by p.value";
	}

// users
	private static String FIND_ALL_USERS = createFindAllObjectsOfTypeSelect(DbLayer.OBJECT_TYPE_USER, User.UPROP_LOGIN);

	public Integer createNewUser() throws PersistenceException {
		return create(OBJECT_TYPE_USER);
	}

	public Integer userId(String login) throws PersistenceException {
		return objectId(OBJECT_TYPE_USER, User.UPROP_LOGIN, login);
	}

	public Map<Integer, String> userIdLoginMap() throws PersistenceException {
		Map<Integer, String> map = new LinkedHashMap<Integer, String>();
		PreparedStatement pstmt = null;
		try {
			pstmt = DbTransaction.getConnection().prepareStatement(FIND_ALL_USERS);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				map.put(rs.getInt("id"), rs.getString("value"));
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			forcedStatementClose(pstmt);
		}
		return map;
	}

// boards
	private static String FIND_ALL_BOARDS = createFindAllObjectsOfTypeSelect(DbLayer.OBJECT_TYPE_BOARD, Board.BPROP_NAME);

	public Set<String> boardNames() throws PersistenceException {
		Set<String> set = new HashSet<String>();
		PreparedStatement pstmt = null;
		try {
			pstmt = DbTransaction.getConnection().prepareStatement(FIND_ALL_BOARDS);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				set.add(rs.getString("value"));
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			forcedStatementClose(pstmt);
		}
		return set;
	}

	public Integer boardId(String name) throws PersistenceException {
		return objectId(OBJECT_TYPE_BOARD, Board.BPROP_NAME, name);
	}

// locations
	private static String FIND_ALL_LOCATIONS = createFindAllObjectsOfTypeSelect(DbLayer.OBJECT_TYPE_LOCATION, Location.LPROP_NAME);

	public Set<String> locationNames() throws PersistenceException {
		Set<String> set = new HashSet<String>();
		PreparedStatement pstmt = null;
		try {
			pstmt = DbTransaction.getConnection().prepareStatement(FIND_ALL_LOCATIONS);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				set.add(rs.getString("value"));
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			forcedStatementClose(pstmt);
		}
		return set;
	}

	public Integer locationId(String name) throws PersistenceException {
		return objectId(OBJECT_TYPE_LOCATION, Location.LPROP_NAME, name);
	}

	public Integer createNewLocation() throws PersistenceException {
		return create(OBJECT_TYPE_LOCATION);
	}

// texts
	public Integer createNewText() throws PersistenceException {
		return create(OBJECT_TYPE_TEXT);
	}

	public List<Text> loadTexts(Integer objectId, int relationType, String sortingOrder) throws PersistenceException {
		List<Text> texts = new LinkedList<Text>();
		PreparedStatement pstmt = null;
		String sortOrder;
		if (sortingOrder != null && isDescendOrder(sortingOrder)) {
			sortOrder = "desc";
		} else {
			sortOrder = "asc";
		}
		try {
			pstmt = DbTransaction.getConnection().prepareStatement("select" +
				"   t.id" +
				" from" +
				"   object_text ot inner join objects t on (ot.text_id = t.id)" +
				" where" +
				"   ot.object_id=? and ot.ttype=?" +
				" order by t.id " + sortOrder);
			pstmt.setInt(1, objectId);
			pstmt.setInt(2, relationType);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				texts.add(new Text(rs.getInt("id")));
			}
			pstmt.close();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			forcedStatementClose(pstmt);
		}
		return texts;
	}

	private boolean isDescendOrder(String sortingOrder) {
		return (SortOrderProperty.DESC.startsWith(sortingOrder) || SortOrderProperty.NEW.startsWith(sortingOrder));
	}

	public void storeTextRelation(TextRelation textRelation) throws PersistenceException {
		PreparedStatement pstmt = null;
		try {
			pstmt = DbTransaction.getConnection().prepareStatement("insert into object_text (object_id, ttype, text_id) values (?, ?, ?)");
			int i = 1;
			pstmt.setInt(i++, textRelation.getObjectId());
			pstmt.setInt(i++, textRelation.getType());
			pstmt.setInt(i++, textRelation.getTextId());
			pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			forcedStatementClose(pstmt);
		}
	}

	public void removeTextRelation(TextRelation textRelation) throws PersistenceException {
		PreparedStatement pstmt = null;
		try {
			pstmt = DbTransaction.getConnection().prepareStatement("delete from object_text where object_id = ? and ttype = ? and text_id = ?");
			int i = 1;
			pstmt.setInt(i++, textRelation.getObjectId());
			pstmt.setInt(i++, textRelation.getType());
			pstmt.setInt(i++, textRelation.getTextId());
			pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			forcedStatementClose(pstmt);
		}
	}

	public List<TextRelation> loadTextRelations(Text text) throws PersistenceException {
		List<TextRelation> l = new LinkedList<TextRelation>();
		PreparedStatement pstmt = null;
		try {
			pstmt = DbTransaction.getConnection().prepareStatement("select object_id, ttype, last_accessed from object_text where text_id=?");
			pstmt.setInt(1, text.getId());
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				l.add(new TextRelation(rs.getInt("object_id"),
					text.getId(),
					rs.getInt("ttype"),
					rs.getDate("last_accessed")));
			}
			pstmt.close();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			forcedStatementClose(pstmt);
		}
		return l;
	}

//// private service stuff
	static {
		logger = Logger.getLogger("nt.dbLayer");
	}

	/**
	 * Called possibly also for non-opened statement.
	 */
	private static void forcedStatementClose(Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
			}
		}
	}
}
